using System;
using System.Collections.Generic ;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// Summary description for Provider
/// </summary>
public class Provider
{
    protected string serverConnString;

    public string ServerConnString
    {
        get { return serverConnString; }
        set { serverConnString = value; }
    }

    public Provider() { }
    public Provider(string connStr)
    {
        serverConnString = connStr;
    }
    public void ExeQuery(string sql)
    {
        SqlConnection conn = new SqlConnection(ServerConnString);
        conn.Open();
        SqlCommand comm = new SqlCommand(sql, conn);
        comm.ExecuteNonQuery();
        conn.Close();
        conn.Dispose();
    }

    public DataTable ExecuteQuery(string sql)
    {
        try
        {
            SqlConnection conn = new SqlConnection(ServerConnString);
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            SqlDataAdapter ad = new SqlDataAdapter(comm);
            DataTable dt = new DataTable();
            ad.Fill(dt);
            conn.Close();
            conn.Dispose();
            return dt;
        }
        catch
        {
            return null;
        }
    }
    public SqlDataReader ExecuteStoreProcedure_returnDataReader(string nameSP)
    {
        try
        {
            SqlDataReader sqlDataReader;
            SqlConnection sqlConn = new SqlConnection(ServerConnString);
            sqlConn.Open();
            SqlCommand sqlComm = new SqlCommand();
            sqlComm.Connection = sqlConn;
            sqlComm.CommandText = nameSP;
            sqlComm.CommandType = CommandType.StoredProcedure;
            sqlDataReader = sqlComm.ExecuteReader();
            sqlConn.Close();
            sqlConn.Dispose();
            return sqlDataReader;
        }
        catch
        {
            return null;
        }
    }
    public SqlDataReader ExecuteStoreProcedure_returnDataReader(string nameSP, string[] paraName, object[] paraValue)
    {
        try
        {
            SqlDataReader sqlDataReader;
            SqlConnection sqlConn = new SqlConnection(ServerConnString);
            sqlConn.Open();
            SqlCommand sqlComm = new SqlCommand();
            for (int i = 0; i < paraName.Length; i++)
            {
                sqlComm.Parameters.AddWithValue(paraName[i], paraValue[i]);
            }
            sqlComm.Connection = sqlConn;
            sqlComm.CommandText = nameSP;
            sqlComm.CommandType = CommandType.StoredProcedure;
            sqlDataReader = sqlComm.ExecuteReader();
            sqlConn.Close();
            sqlConn.Dispose();
            return sqlDataReader;
        }
        catch
        {
            return null;
        }
    }
    public DataTable ExecuteStoreProcedure(string nameSP)
    {
        try
        {
            DataTable dt = new DataTable();
            SqlConnection sqlConn = new SqlConnection(ServerConnString);
            sqlConn.Open();
            SqlCommand sqlComm = new SqlCommand();
            sqlComm.Connection = sqlConn;
            sqlComm.CommandText = nameSP;
            sqlComm.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter sqlDataAdater = new SqlDataAdapter(sqlComm);
            sqlDataAdater.Fill(dt);
            sqlConn.Close();
            sqlConn.Dispose();
            return dt;
        }
        catch
        {
            return null;
        }
    }
    public DataTable ExecuteStoreProcedure(string nameSP, string[] paraName, object[] paraValue)
    {
        try
        {
            DataTable dt = new DataTable();
            SqlConnection sqlConn = new SqlConnection(ServerConnString);
            sqlConn.Open();
            SqlCommand sqlComm = new SqlCommand();
            for (int i = 0; i < paraName.Length; i++)
            {
                sqlComm.Parameters.AddWithValue(paraName[i], paraValue[i]);
            }
            sqlComm.Connection = sqlConn;
            sqlComm.CommandText = nameSP;
            sqlComm.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter sqlDataAdater = new SqlDataAdapter(sqlComm);
            sqlDataAdater.Fill(dt);
            sqlConn.Close();
            sqlConn.Dispose();
            return dt;
        }
        catch
        {
            return null;
        }
    }
}